using System;
using System.Data;
using System.Data.SqlClient;
using Framework.Library;
using System.Web.UI.WebControls;
using Framework.Library.Utilities;

namespace Framework.Module.UpDownLoad.Library
{
    public class Files_Controller
    {
        public static DataTable GetAll()
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Share_Files_GetAll", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
		public static void Delete (int _iD)
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Share_Files_Delete", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@ID", _iD);
			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally
			{
				dbConn.Close();
			}
		}
        public static int Insert(Files_Info _share_FileInfo)
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Share_Files_Insert", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Name", _share_FileInfo.Name);
            dbCmd.Parameters.AddWithValue("@Title", _share_FileInfo.Title);
            dbCmd.Parameters.AddWithValue("@Size", _share_FileInfo.Size);
            dbCmd.Parameters.AddWithValue("@Description", _share_FileInfo.Description);
            dbCmd.Parameters.AddWithValue("@Report", _share_FileInfo.Report);
            dbCmd.Parameters.AddWithValue("@CategoryID", _share_FileInfo.CategoryID);
            dbCmd.Parameters.AddWithValue("@FileType", _share_FileInfo.FileType);
            dbCmd.Parameters.AddWithValue("@DownloadNumber", _share_FileInfo.DownloadNumber);
            dbCmd.Parameters.AddWithValue("@DateCreate", _share_FileInfo.DateCreate);
            dbCmd.Parameters.AddWithValue("@Uploader", _share_FileInfo.Uploader);
			dbCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
			try {
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return (int)dbCmd.Parameters["@RETURN_VALUE"].Value;
			}
			finally 
			{
				dbConn.Close();
			}
		}

        public static void Update(Files_Info _share_FileInfo)
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Share_Files_Update", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@ID", _share_FileInfo.ID);
            //dbCmd.Parameters.AddWithValue("@Name", _share_FileInfo.Name);
            dbCmd.Parameters.AddWithValue("@Title", _share_FileInfo.Title);
            //dbCmd.Parameters.AddWithValue("@Size", _share_FileInfo.Size);
            dbCmd.Parameters.AddWithValue("@Description", _share_FileInfo.Description);
            //dbCmd.Parameters.AddWithValue("@Report", _share_FileInfo.Report);
            //dbCmd.Parameters.AddWithValue("@CategoryID", _share_FileInfo.CategoryID);
            //dbCmd.Parameters.AddWithValue("@FileType", _share_FileInfo.FileType);
            //dbCmd.Parameters.AddWithValue("@DownloadNumber", _share_FileInfo.DownloadNumber);
            //dbCmd.Parameters.AddWithValue("@DateCreate", _share_FileInfo.DateCreate);
            //dbCmd.Parameters.AddWithValue("@Uploader", _share_FileInfo.Uploader);
			try {
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally
			{
				dbConn.Close();
			}
		}

        public static Files_Info GetInfo(int _iD)
		{
            Files_Info retVal = null;
 			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Share_Files_GetInfo", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@ID", _iD);
			try 
			{
				dbConn.Open();
				SqlDataReader dr = dbCmd.ExecuteReader();
				if (dr.Read()) 
				{
                    retVal = new Files_Info();
					retVal.ID=Convert.ToInt32(dr["ID"]);
					retVal.Name=Convert.ToString(dr["Name"]);
					retVal.Title=Convert.ToString(dr["Title"]);
					retVal.Size=Convert.ToInt32(dr["Size"]);
					retVal.Description=Convert.ToString(dr["Description"]);
					retVal.Report=Convert.ToBoolean(dr["Report"]);
					retVal.CategoryID=Convert.ToInt32(dr["CategoryID"]);
					retVal.FileType=Convert.ToString(dr["FileType"]);
					retVal.DownloadNumber=Convert.ToInt32(dr["DownloadNumber"]);
					retVal.DateCreate=Convert.ToDateTime(dr["DateCreate"]);
                    retVal.Uploader =Convert.ToString(dr["Uploader"]);
				}
				if (dr != null)	dr.Close();
			}
			finally
			{
				dbConn.Close();
			}
			return retVal;
		}

        public static DataTable GetAllByCategoryID(int CatID)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Share_Files_GetAllByCategoryID", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@CatID", CatID);
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static void FillToListBox(ListItemCollection lstItems, DataTable source, int rootId)
        {
            DataRow[] drCommands = source.Select("ParentID = " + rootId);
            foreach (DataRow row in drCommands)
            {
                if (AppEnv.GetLanguage() != "vi-VN")
                {
                    ListItem rootItem = new ListItem(row["NameENG"].ToString(), row["ID"].ToString());
                    rootItem.Attributes.Add("Level", "0");                    
                    lstItems.Add(rootItem);
                    LoadForCurListItem(lstItems, rootItem, source);
                }
                else
                {
                    ListItem rootItem = new ListItem(row["NameVIE"].ToString(), row["ID"].ToString());
                    rootItem.Attributes.Add("Level", "0");
                    lstItems.Add(rootItem);
                    LoadForCurListItem(lstItems, rootItem, source);
                }


            }
            lstItems.Insert(0, new ListItem("Root", "0"));
        }

        private static void LoadForCurListItem(ListItemCollection lstItems, ListItem curItem, DataTable source)
        {

            int curCommandId = ConvertUtility.ToInt32(curItem.Value);
            int level = ConvertUtility.ToInt32(curItem.Attributes["Level"]);
            level++;
            DataRow[] drChildCommands = source.Select("ParentID = " + curCommandId);
            foreach (DataRow row in drChildCommands)
            {
                if (AppEnv.GetLanguage() != "vi-VN")
                {
                    ListItem item = new ListItem(MiscUtility.StringIndent(level) + row["NameENG"].ToString(), row["ID"].ToString());
                    item.Attributes.Add("Level", level.ToString());
                    lstItems.Add(item);
                    LoadForCurListItem(lstItems, item, source);
                }
                else
                {
                    ListItem item = new ListItem(MiscUtility.StringIndent(level) + row["NameVIE"].ToString(), row["ID"].ToString());
                    item.Attributes.Add("Level", level.ToString());
                    lstItems.Add(item);
                    LoadForCurListItem(lstItems, item, source);
                }

            }
        }
            
    }
}
